package itesm.coler.data;

import itesm.coler.models.Team;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MYSQLTeamDAO implements TeamDAO {
	Connection connection = null;
	
	public MYSQLTeamDAO() {
	}

	public Connection getConnection(){
		if (connection == null)
		    connection = MySQLDAOFactory.createConnection();
		return connection;
	}
	
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	
	// The following methods can use
    // MySQLDAOFactory.createConnection() 
    // to get a connection as required
	public Team findTeamById(int teamId) throws DAOException {		 
		 PreparedStatement find = null;
	     ResultSet results= null;
	     Team team= null;
	     try{    	
			 connection = getConnection();
			 if (connection!=null){
				 find = connection.prepareStatement("select * from team where teamid = ?");
				 find.setInt(1,teamId);
				 results = find.executeQuery();
						  if (results.next()) {
							  team = mapResultSetToObject(results);						  			  	
						  }
				}
			}catch(SQLException e){
				throw new DAOException(e);
			} finally {
				DAOUtil.close(connection,find,results);
			}
			return team;
	}
	
	public Team findTeamByName(String teamName) throws DAOException {
		 PreparedStatement find = null;
	     ResultSet results= null;
	     Team team= null;
	     try{    	
	    	 connection = getConnection();
			 if (connection!=null){
				 find = connection.prepareStatement("select * from team where teamname = ?");
				 find.setString(1,teamName);
				 results = find.executeQuery();
						  if (results.next()) {
							  team = mapResultSetToObject(results);						  			  	
						  }
				}
			}catch(SQLException e){
				throw new DAOException(e);
			} finally {
				DAOUtil.close(connection,find,results);
			}
			return team;
	}
	
	

	public ArrayList<Team> findAll() throws DAOException {
		PreparedStatement findAll = null;
		ResultSet results = null;
		ArrayList<Team> teams = null;
	    try {
	    	 connection = getConnection();
		     findAll = connection.prepareStatement("select * from team");
		     synchronized(findAll){
			     results = findAll.executeQuery();
			     teams = new ArrayList<Team>();			
			     while(results.next()){
				   Team team = mapResultSetToObject(results);		     
				   teams.add(team);
				}
		     }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findAll,results);
		   }			
		   return teams;
	}
	    
	public ArrayList<Team> findAllByCourse(String courseId) throws DAOException {
		PreparedStatement findAllByCourse = null;
		ResultSet results = null;
		ArrayList<Team> teams = null;
	    try {
	    	 connection = getConnection();
		     findAllByCourse = connection.prepareStatement("select * from team where courseid=?");
		     synchronized(findAllByCourse){
		    	 findAllByCourse.setString(1,courseId);
			     results = findAllByCourse.executeQuery();
			     teams = new ArrayList<Team>();			
			     while(results.next()){
				   Team team = mapResultSetToObject(results);		     
				   teams.add(team);
				}
		     }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findAllByCourse,results);
		   }			
		   return teams;
	}
	
	public Team findTeamNamesByCourse(String courseId) throws DAOException {
		 PreparedStatement find = null;
	     ResultSet results= null;
	     Team team= null;
	     try{    	
	    	 connection = getConnection();
			 if (connection!=null){
				 find = connection.prepareStatement("select teamname from team where courseid = ?");
				 find.setString(1,courseId);
				 results = find.executeQuery();
						  if (results.next()) {
							  team = mapResultSetToObject(results);						  			  	
						  }
				}
			}catch(SQLException e){
				throw new DAOException(e);
			} finally {
				DAOUtil.close(connection,find,results);
			}
			return team;
	}
	
	
	
	 public int insertTeam(Team team) {
		    // Implement insert course here.
		    // Return 0 if OK
		    // or a -1 on error	  		 	
		    PreparedStatement insert = null;
		    int result=-1; //error
			 try { 
				    connection = getConnection();
				   // connection = ds.getConnection();
					insert = connection.prepareStatement("insert into team(teamid,teamname,active,courseid) values(?,?,?,?);");
					insert.setInt(1, team.getTeamId());
					insert.setString(2, team.getTeamName());
					insert.setInt(3, 1); //active
					insert.setString(4, team.getCourse().getCourseId());
					result = insert.executeUpdate();
					insert.close();
					connection.close();
			} catch (SQLException e) {
				  throw new DAOException(e);
	        } finally {
	              DAOUtil.close(connection, insert);
	        }
			return result;
	 }
		  
	 public boolean updateTeam(Team team) throws DAOException {
		    // implement update record here using data
		    // from the user   Object
		    // Return true on success, false on failure or
		    // error
		    PreparedStatement update = null;
		    boolean result = true;
			  try {
				  connection = getConnection();
				  update = connection.prepareStatement("UPDATE team SET coursename = ?, courseid = ? WHERE teamid = ?");
				  update.setString(1, team.getTeamName());
				  update.setString(2,team.getCourse().getCourseId());
				  update.setInt(3, team.getTeamId());
				  int affectedRows = update.executeUpdate();
				  if (affectedRows == 0) {
					    result = false;
		                throw new DAOException("Updating team failed, no rows affected.");
		            }
			} catch (SQLException e) {
				 result = false;
				 throw new DAOException(e);
		    } finally {
				DAOUtil.close(connection,update);
			}
			 return result;
		  }
	 public boolean deleteTeam(int teamId) throws DAOException {
		  // Implement delete user here
		  // Return true on success, false on failure
		  PreparedStatement deleteTeam = null;
         boolean result = true;      
		  try {
			  connection = getConnection();
			    deleteTeam= connection.prepareStatement("delete from team where teamid = ?");
				deleteTeam.setInt(1, teamId);
				int affectedRows = deleteTeam.executeUpdate();
			    if (affectedRows == 0) {
			    	    result = false;
		                throw new DAOException("Deleting team failed, no rows affected.");
		         }
			  } catch (SQLException e) {
				  result = false;
				  throw new DAOException(e);
			  } finally {
					DAOUtil.close(connection,deleteTeam);
			} 
           return result;
	} 
	 
	 
	  public boolean updateTeamActiveStatus(int teamId, boolean status) throws DAOException {
		  PreparedStatement activeStatus = null;
		  boolean result = true;      
				  try {
					    connection = getConnection();
					    activeStatus = connection.prepareStatement("UPDATE team SET active = ? WHERE teamid = ?;");
						activeStatus.setBoolean(1, status);
						activeStatus.setInt(2, teamId);
						result = activeStatus.execute();
						connection.close();
					  } catch (SQLException e) {
						  result = false;
						  throw new DAOException(e);
					  } finally {
							DAOUtil.close(connection,activeStatus);
					} 
		            return result;
		            
	  }
	  
		
		 public Team mapResultSetToObject(ResultSet results) throws SQLException {
			  Team team = new Team();
			  Integer teamId = results.getInt("teamid");
			  team.setTeamId(teamId.intValue());
		      team.setActive(results.getBoolean("active"));
		      team.setTeamName(results.getString("teamname"));
	          String courseId = results.getString("courseid");
	          DAOFactory MySQLFactory = DAOFactory.getDAOFactory(DAOFactory.MySQL);
	 	  	  CourseDAO courseDAO = MySQLFactory.getCourseDAO();
	          team.setCourse(courseDAO.findCourseById(courseId));
	          UserDAO userDAO = MySQLFactory.getUserDAO();
	          team.setUsers(userDAO.findAllByTeam(teamId));
	//	      course.setProfessorUserId(professorUserId);
			  return team;
		 }		 

}
